Skip Headers

Oracle® Call Interface Programmer's Guide, 10g Release 2 (10.2) Part Number B14250-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Statement Functions

This section describes the statement functions.

Table 16-2 Statement Functions

Function | Purpose
--- | ---
OCIStmtExecute() | Sends statements to server for execution
OCIStmtFetch() | Fetches rows from a query (deprecated)
OCIStmtFetch2() | Fetches rows from a query
OCIStmtGetPieceInfo() | Gets piece information for piecewise operations
OCIStmtPrepare() | Prepares a SQL or PL/SQL statement for execution.
OCIStmtPrepare2() | Prepares a SQL or PL/SQL statement for execution.
OCIStmtRelease() | Releases the statement handle.
OCIStmtSetPieceInfo() | Sets piece information for piecewise operations

## OCIStmtExecute()

Purpose

This call associates an application request with a server.

Syntax

    sword OCIStmtExecute ( OCISvcCtx           *svchp,
                           OCIStmt             *stmtp,
                           OCIError            *errhp,
                           ub4                 iters,
                           ub4                 rowoff,
                           CONST OCISnapshot   *snap_in,
                           OCISnapshot         *snap_out,
                           ub4                 mode );

Parameters

svchp (IN/OUT)

  Service context handle.

stmtp (IN/OUT)

  An statement handle. It defines the statement and the associated data to be executed at the server. It is invalid to pass in a statement handle that has bind of datatypes only supported in release 8.x or later when `svchp` points to an Oracle7 server.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

iters (IN)

  For non-`SELECT` statements, the number of times this statement is executed is equal to `iters` - `rowoff`.

  For `SELECT` statements, if `iters` is nonzero, then defines must have been done for the statement handle. The execution fetches `iters` rows into these predefined buffers and prefetches more rows depending upon the prefetch row count. If you do not know how many rows the `SELECT` statement will retrieve, set `iters` to zero.

  This function returns an error if `iters`=0 for non-`SELECT` statements.

  Note: For array DML operations, set `iters` <= 32767 to get better performance.

rowoff (IN)

  The starting index from which the data in an array bind is relevant for this multiple row execution.

snap_in (IN)

  This parameter is optional. if supplied, must point to a snapshot descriptor of type `OCI_DTYPE_SNAP`. The contents of this descriptor must be obtained from the `snap_out` parameter of a previous call. The descriptor is ignored if the SQL is not a `SELECT`. This facility allows multiple service contexts to ORACLE to see the same consistent snapshot of the database's committed data. However, uncommitted data in one context is not visible to another context even using the same snapshot.

snap_out (OUT)

  This parameter optional. if supplied, must point to a descriptor of type `OCI_DTYPE_SNAP`. This descriptor is filled in with an opaque representation which is the current ORACLE "system change number" suitable as a `snap_in` input to a subsequent call to `OCIStmtExecute()`. This descriptor should not be used longer than necessary in order to avoid "snapshot too old" errors.

mode (IN)

  The modes are:

- `OCI_BATCH_ERRORS` - See "Batch Error Mode", for information about this mode.

- `OCI_COMMIT_ON_SUCCESS` - When a statement is executed in this mode, the current transaction is committed after execution, if execution completes successfully.

- `OCI_DEFAULT` - Calling `OCIStmtExecute()` in this mode executes the statement. It also implicitly returns describe information about the select-list.

- `OCI_DESCRIBE_ONLY` - This mode is for users who wish to describe a query prior to execution. Calling `OCIStmtExecute()` in this mode does not execute the statement, but it does return the select-list description. To maximize performance, it is recommended that applications execute the statement in default mode and use the implicit describe which accompanies the execution.

- `OCI_EXACT_FETCH` - Used when the application knows in advance exactly how many rows it will be fetching. This mode turns prefetching off for Oracle release 8 or later mode, and requires that defines be done before the execute call. Using this mode cancels the cursor after the desired rows are fetched and may result in reduced server-side resource usage.

- `OCI_PARSE_ONLY` - This mode allows the user to parse the query prior to execution. Executing in this mode parses the query and returns parse errors in the SQL, if any. Users must note that this will involve an additional round trip to the server. To maximize performance, it is recommended that the user execute the statement in the default mode which, as part of a bundled operation, parses the statement.

- `OCI_STMT_SCROLLABLE_READONLY` - Required for the result set to be scrollable. The result set cannot be updated. See "Fetching Results". Cannot be used with any other mode.

The modes are not mutually exclusive and can be used together, except for `OCI_STMT_SCROLLABLE_READONLY`.

Comments

This function is used to execute a prepared SQL statement. Using an execute call, the application associates a request with a server.

If a `SELECT` statement is executed, the description of the select-list is available implicitly as a response. This description is buffered on the client side for describes, fetches and define type conversions. Hence it is optimal to describe a select list only after an execute.

See Also: "Describing Select-list Items"

Also for `SELECT` statements, some results are available implicitly. Rows will be received and buffered at the end of the execute. For queries with small row count, a prefetch causes memory to be released in the server if the end of fetch is reached, an optimization that may result in memory usage reduction. Set attribute call has been defined to set the number of rows to be prefetched for each result set.

For `SELECT` statements, at the end of the execute, the statement handle implicitly maintains a reference to the service context on which it is executed. It is the user's responsibility to maintain the integrity of the service context. The implicit reference is maintained until the statement handle is freed or the fetch is cancelled or an end of fetch condition is reached.

Note: If output variables are defined for a `SELECT` statement before a call to `OCIStmtExecute()`, the number of rows specified by `iters` will be fetched directly into the defined output buffers and additional rows equivalent to the prefetch count will be prefetched. If there are no additional rows, then the fetch is complete without calling `OCIStmtFetch()`.

Related Functions

OCIStmtPrepare()

## OCIStmtFetch()

Purpose

Fetches rows from a query. Users are encouraged to use the new fetch call `OCIStmtFetch2()`. This call is deprecated.

Syntax

    sword OCIStmtFetch ( OCIStmt     *stmtp,
                         OCIError    *errhp,
                         ub4         nrows,
                         ub2         orientation,
                         ub4         mode );

Parameters

stmtp (IN)

  A statement (application request) handle.

errhp (IN)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

nrows (IN)

  Number of rows to be fetched from the current position.

orientation (IN)

  Prior to release 9.0, the only acceptable value is `OCI_FETCH_NEXT`, which is also the default value.

mode (IN)

  Pass as `OCI_DEFAULT`.

Comments

The fetch call is a local call, if prefetched rows suffice. However, this is transparent to the application.

If LOB columns are being read, LOB locators are fetched for subsequent LOB operations to be performed on these locators. Prefetching is turned off if `LONG` columns are involved.

This function can return `OCI_NO_DATA` on `EOF` and `OCI_SUCCESS_WITH_INFO` when one of the following errors occur:

- ORA-24344 Success with compilation error

- ORA-24345 A truncation or `NULL` fetch error occurred

- ORA-24347 Warning of a `NULL` column in an aggregate function

If you call `OCIStmtFetch()` with the `nrows` parameter set to 0, this cancels the cursor.

Use `OCI_ATTR_ROWS_FETCHED` to find the number of rows that were successfully fetched into the user's buffers in the last fetch call.

Related Functions

OCIStmtExecute()

## OCIStmtFetch2()

Purpose

This fetches a row from the (scrollable) result set. You are encouraged to use this fetch call instead of the deprecated call `OCIStmtFetch()`.

Syntax

    sword OCIStmtFetch2 ( OCIStmt     *stmthp,
                          OCIError    *errhp,
                          ub4         nrows,
                          ub2         orientation,
                          sb4         fetchOffset,
                          ub4         mode );

Parameters

stmthp (IN/OUT)

  This is the statement handle of the (scrollable) result set.

errhp (IN/OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in event of an error.

nrows (IN)

  Number of rows to be fetched from the current position.

orientation (IN)

  The acceptable values are:

- `OCI_DEFAULT` - has the same effect as `OCI_FETCH_NEXT`

- `OCI_FETCH_CURRENT` - gets the current row.

- `OCI_FETCH_NEXT` - gets the next row from the current position. It is the default (has the same effect as `OCI_DEFAULT`). Use for a non-scrollable statement handle.

- `OCI_FETCH_FIRST` - gets the first row in the result set.

- `OCI_FETCH_LAST` - gets the last row in the result set.

- `OCI_FETCH_PRIOR` - positions the result set on the previous row from the current row in the result set. You can fetch multiple rows using this mode, from the "previous row" also.

- `OCI_FETCH_ABSOLUTE` will fetch the row number (specified by `fetchOffset` parameter) in the result set using absolute positioning.

- `OCI_FETCH_RELATIVE` will fetch the row number (specified by `fetchOffset` parameter) in the result set using relative positioning.

fetchOffset (IN)

  The offset to be used with the orientation parameter for changing the current row position.

mode (IN)

  Pass in `OCI_DEFAULT`.

Comments

The fetch call works similarly to the `OCIStmtFetch()` call with the addition of the `fetchOffset` parameter. It can be used on any statement handle, whether it is scrollable or not. For a non-scrollable statement handle, the only acceptable value of `orientation` is `OCI_FETCH_NEXT`, and the `fetchOffset` parameter will be ignored.

For new applications you are encouraged to use this new call, `OCIStmtFetch2()`.

A `fetchOffset` with `orientation` set to `OCI_FETCH_RELATIVE` is equivalent to all of the following:

- `OCI_FETCH_CURRENT` with a value of `fetchOffset` equal to 0,

- `OCI_FETCH_NEXT` with a value of `fetchOffset` equal to 1,

- `OCI_FETCH_PRIOR` with a value of `fetchOffset` equal to -1.

`OCI_ATTR_ROW_COUNT` contains the highest absolute row value that was fetched.

All other orientation modes besides `OCI_FETCH_ABSOLUTE` and `OCI_FETCH_RELATIVE` will ignore the `fetchOffset` value.

This call can also be used to find out the number of rows in the result set by using `OCI_FETCH_LAST`, and then calling `OCIAttrGet()` on `OCI_ATTR_CURRENT_POSITION`. But the response time of this call can be high.

The return codes are the same as for `OCIStmtFetch()`, except that `OER(1403)` with return code `OCI_NO_DATA` will be returned every time a fetch on a scrollable statement handle (or execute) is made and not all rows requested by the application could be fetched.

If you call `OCIStmtFetch2()` with the `nrows` parameter set to 0, this cancels the cursor.

The scrollable statement handle will need to be explicitly cancelled (that is, fetch with 0 rows) or freed in order to release server-side resources for the scrollable cursor. A non-scrollable statement handle is implicitly cancelled on receiving the OER(1403).

Use `OCI_ATTR_ROWS_FETCHED` to find the number of rows that were successfully fetched into the user's buffers in the last fetch call.

See Also: "Scrollable Cursors in OCI" for more information on this topic

Related Functions

OCIStmtExecute(), OCIBindByPos()

## OCIStmtGetPieceInfo()

Purpose

Returns piece information for a piecewise operation.

Syntax

    sword OCIStmtGetPieceInfo( CONST OCIStmt  *stmtp,
                               OCIError       *errhp,
                               dvoid          **hndlpp,
                               ub4            *typep,
                               ub1            *in_outp,
                               ub4            *iterp,
                               ub4            *idxp,
                               ub1            *piecep );

Parameters

stmtp (IN)

  The statement when executed returned `OCI_NEED_DATA`.

errhp (OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

hndlpp (OUT)

  Returns a pointer to the bind or define handle of the bind or define whose runtime data is required or is being provided.

typep (OUT)

  The type of the handle pointed to by `hndlpp`: `OCI_HTYPE_BIND` (for a bind handle) or `OCI_HTYPE_DEFINE` (for a define handle).

in_outp (OUT)

  Returns `OCI_PARAM_IN` if the data is required for an IN bind value. Returns `OCI_PARAM_OUT` if the data is available as an OUT bind variable or a define position value.

iterp (OUT)

  Returns the row number of a multiple row operation.

idxp (OUT)

  The index of an array element of a PL/SQL array bind operation.

piecep (OUT)

  Returns one of the following defined values `OCI_ONE_PIECE`, `OCI_FIRST_PIECE`, `OCI_NEXT_PIECE` and `OCI_LAST_PIECE`.

Comments

When an execute or fetch call returns `OCI_NEED_DATA` to get or return a dynamic bind or define value or piece, `OCIStmtGetPieceInfo()` returns the relevant information: bind or define handle, iteration, index number and which piece.

See Also: "Runtime Data Allocation and Piecewise Operations in OCI" for more information about using `OCIStmtGetPieceInfo()`.

Related Functions

OCIAttrGet(), OCIAttrSet(), OCIStmtExecute(), OCIStmtFetch(), OCIStmtSetPieceInfo()

## OCIStmtPrepare()

Purpose

This call prepares a SQL or PL/SQL statement for execution.

Syntax

    sword OCIStmtPrepare ( OCIStmt       *stmtp,
                           OCIError      *errhp,
                           CONST text    *stmt,
                           ub4           stmt_len,
                           ub4           language,
                           ub4           mode );

Parameters

stmtp (IN)

  A statement handle associated with the statement to be executed. By default, it contains the encoding setting in the environment handle from which it is derived. A statement can be prepared in UTF-16 encoding only in a UTF-16 environment.

errhp (IN)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

stmt (IN)

  SQL or PL/SQL statement to be executed. Must be a `NULL`-terminated string. That is, the ending character is a number of NULL bytes, depending on the encoding. The statement must be in the encoding specified by the `charset` parameter of a previous call to `OCIEnvNlsCreate()`.

  Always cast the parameter to (`text *)`. After a statement has been prepared in UTF-16, the character set for the bind and define buffers will default to UTF-16.

stmt_len (IN)

  Length of the statement in characters or in number of bytes, depending on the encoding. Must not be zero.

language (IN)

  Specifies V7, or native syntax. Possible values are:

- `OCI_V7_SYNTAX` - V7 ORACLE parsing syntax

- `OCI_NTV_SYNTAX` - syntax depends upon the version of the server.

mode (IN)

  Similar to the `mode` in the `OCIEnvCreate()` call, but this one has higher priority because it can override the "naturally" inherited mode setting.

  The only possible value is:

- `OCI_DEFAULT` - default mode. The statement handle `stmtp` uses whatever is specified by its parent environment handle.

Comments

An OCI application uses this call to prepare a SQL or PL/SQL statement for execution. The `OCIStmtPrepare()` call defines an application request.

The `mode` parameter determines whether the statement content is encoded as UTF-16 or not. The statement length is in number of codepoints or in number of bytes, depending on the encoding.

While the statement handle inherits the encoding setting from the parent environment handle, the `mode` for this call can also change the encoding setting for the statement handle itself.

Data values for this statement initialized in subsequent bind calls will be stored in a bind handle which use settings in this statement handle as default.

This call does not create an association between this statement handle and any particular server.

See Also: "Preparing Statements" for more information about using this call.

Related Functions

OCIAttrGet(), OCIStmtExecute()

## OCIStmtPrepare2()

Purpose

This call prepares a SQL or PL/SQL statement for execution. The user has the option of using the statement cache, if it has been enabled.

Syntax

    sword OCIStmtPrepare2 ( OCISvcCtx      *svchp,
                            OCIStmt        **stmthp,
                            OCIError       *errhp,
                            CONST OraText  *stmttext,
                            ub4            stmt_len,
                            CONST OraText  *key,
                            ub4            keylen,
                            ub4            language,
                            ub4            mode );

Parameters

svchp (IN)

  The service context to be associated with the statement.

errhp (IN)

  A pointer to the error handle for diagnostics.

stmthp (OUT)

  Pointer to the statement handle returned.

stmttext (IN)

  The statement text. The semantics of the `stmttext` are same as that of `OCIStmtPrepare()`, that is, the string must be `NULL`-terminated.

stmt_len (IN)

  The statement text length.

key (IN)

  For statement caching only. The key to the returned statement in the cache. This can be used for future calls to `OCIStmtPrepare2()`, in which case there is no need to pass in the statement text and related parameters. If the key is passed in, then the statement text and other parameters are ignored and the search is solely based on the key.

keylen (IN)

  For statement caching only. The length of the key.

language (IN)

  Specifies V7, or native syntax. Possible values are:

- `OCI_V7_SYNTAX` - V7 ORACLE parsing syntax

- `OCI_NTV_SYNTAX` - syntax depends upon the version of the server.

mode (IN)

  This function can be used with and without statement caching. This is determined at the time of connection or session pool creation. If caching is enabled for a session, then all statements in the session will have caching enabled, and if caching is not enabled, then all statements will not be cached.

  The valid modes are:

- `OCI_DEFAULT` - For non-caching, this is the only valid setting. If the statement is not found in the cache, it allocates a new statement handle and prepares the statement handle for execution. If it is not found and

  - only the text has been supplied: a new statement will be allocated and prepared and returned. The tag will be `NULL`. `OCI_SUCCESS` will be returned.

  - only the tag has been supplied: `stmthp` will be `NULL`. `OCI_ERROR` will be returned.

  - both text and key were supplied: a new statement will be allocated and prepared and returned. The tag will be `NULL`. `OCI_SUCCESS_WITH_INFO` will be returned, as the returned statement differs from the requested statement in that the tag is `NULL`.

- `OCI_PREP2_CACHE_SEARCHONLY` - In this case, if the statement is not found (a NULL statement handle is returned), you must take further action. If the statement is found, `OCI_SUCCESS` will be returned. Otherwise, `OCI_ERROR` will be returned.

- `OCI_PREP2_GET_PLSQL_WARNINGS` - If warnings are enabled in the session and the PL/SQL program is compiled with warnings, then `OCI_SUCCESS_WITH_INFO` will be the return status from the execution. Use `OCIErrorGet()` to find the new error number corresponding to the warnings.

Related Functions

OCIStmtRelease()

## OCIStmtRelease()

Purpose

Releases the statement handle obtained by a call to `OCIStmtPrepare2()`.

Syntax

    sword OCIStmtRelease ( OCIStmt        *stmthp,
                           OCIError       *errhp,
                           CONST OraText  *key,
                           ub4            keylen,
                           ub4            mode );

Parameters

stmthp (IN/OUT)

  The statement handle returned by `OCIStmtPrepare2()`

errhp (IN)

  The error handle used for diagnostics.

key (IN)

  Only valid for statement caching. The key to be associated with the statement in the cache. This can be the key returned by `OCIStmtPrepare2()` or can be a new key. If a `NULL` key is passed in the statement will not be tagged.

keylen (IN)

  Only valid for statement caching. The length of the key.

mode (IN)

  The valid modes are

- `OCI_DEFAULT`

- `OCI_STRLS_CACHE_DELETE` - Only valid for statement caching. The statement will not be kept in the cache any more.

Related Functions

OCIStmtPrepare2()

## OCIStmtSetPieceInfo()

Purpose

Sets piece information for a piecewise operation.

Syntax

    sword OCIStmtSetPieceInfo ( dvoid             *hndlp,
                                ub4               type,
                                OCIError          *errhp,
                                CONST dvoid       *bufp,
                                ub4               *alenp,
                                ub1               piece,
                                CONST dvoid       *indp,
                                ub2               *rcodep );

Parameters

hndlp (IN/OUT)

  The bind/define handle.

type (IN)

  Type of the handle.

errhp (OUT)

  An error handle you can pass to `OCIErrorGet()` for diagnostic information in the event of an error.

bufp (IN/OUT)

  A pointer to a storage containing the data value or the piece when it is an IN bind variable, otherwise `bufp` is a pointer to storage for getting a piece or a value for OUT binds and define variables. For named datatypes or `REF`s, a pointer to the object or `REF` is returned.

alenp (IN/OUT)

  The length of the piece or the value. Do not change this parameter between executions of the same SQL statement.

piece (IN)

  The piece parameter. Valid values:

- `OCI_ONE_PIECE`

- `OCI_FIRST_PIECE`

- `OCI_NEXT_PIECE`

- `OCI_LAST_PIECE`

This parameter is used for IN bind variables only.

indp (IN/OUT)

  Indicator. A pointer to a `sb2` value or pointer to an indicator structure for named datatypes (SQLT_NTY) and `REF`s (SQLT_REF), that is, `*indp` is either an `sb2` or a `dvoid` * depending upon the datatype.

rcodep (IN/OUT)

  Return code.

Comments

When an execute call returns `OCI_NEED_DATA` to get a dynamic IN/OUT bind value or piece, `OCIStmtSetPieceInfo()` sets the piece information: the buffer, the length, which piece is currently being processed, the indicator, and the return code for this column.

See Also: For more information about using `OCIStmtSetPieceInfo()` see the section "Runtime Data Allocation and Piecewise Operations in OCI"

Related Functions

OCIAttrGet(), OCIAttrSet(), OCIStmtExecute(), OCIStmtFetch(), OCIStmtGetPieceInfo()